/** 
 * @memo 函数工具
 * @author lic
 */
Ext.ns("Ext.ux");
Ext.ux.FnTools = Ext.extend(Ext.Window, {
  /**
   * 函数库
   */
	fnlib:{
			"聚合函数":[
			      		{fn:'sum(number)',message:'求和'},
			      		{fn:'avg(number)',message:'求平均值'},
			      		{fn:'count(?)',message:'总数'},
			      		{fn:'max(number)',message:'最大值'},
			      		{fn:'min(number)',message:'最小值'},
			      		{fn:'sum(case when ? then ? else ? end)',message:'条件求和'},
			      		{fn:'avg(case when ? then ? else ? end)',message:'条件求平均值'},
			      		{fn:'count(case when ? then ? else ? end)',message:'条件求总数'},
			      		{fn:'max(case when ? then ? else ? end)',message:'条件求最大值'},
			      		{fn:'min(case when ? then ? else ? end)',message:'条件求最小值'}
			      	 ],
			"字符串处理函数":[
			            {fn:'substr(char,start,end)',message:'截取字符串,char为要处理的字段表达式,start为开始位置,end为结束位置'},
			            {fn:'trim(char,str)',message:'去除两边str字符,str不传默认是空格'},
			            {fn:'ltrim(char,str)',message:'去除左边str字符,str不传默认是空格'},
			            {fn:'rtrim(char,str)',message:'去除右边str字符,str不传默认是空格'},
			            {fn:'length(char)',message:'返回字符串的长度'},
			            {fn:'replace(char,replacechar,replacewith)',message:'字符串替换,char为要处理的字段表达式,replacechar为要替换的内容,replacewith为要替换成的内容'},
			            {fn:'initcap(char)',message:'将首字母大写'},
			            {fn:'lower(char)',message:'将所有字母小写'},
			            {fn:'upper(char)',message:'将所有字母大写'}
			         ],
	        "数字处理函数":[
		                {fn:'round(number,num_digits)',message:'四舍五入,num_digits为小数点位数'},
		                {fn:'floor(number)',message:'舍去小数点部分,例如3.2 -> 3'},
		                {fn:'ceil(number)',message:'舍去小数点部分并进一位,例如3.2 -> 4'}
                     ],
	        "其他常用函数":[
	                    {fn:'nvl(char,replacewith)',message:'处理空值,如果char为NULL，则nvl函数返回replacewith的值，否则返回char的值，如果两个参数的都为NULL ，则返回NULL'},
	                    {fn:'decode(value,if1,then1,if2,then2,if3,then3,...,else)',message:'判断函数，例如decode(sign(salary - 8000),>=0,salary*1.15,<0,salary*1.2,salary) 工资在8000元以下的加20％；工资在8000元或以上的加15％'},
			      		{fn:'dense_rank() over (order by  参数1  desc)',message:'生成对某指标的排序列'}
                      ],
			"数学与三角函数":[
			         	{fn:'abs(number)',message:'返回给定数值的绝对值,即不带符号的数值'},
			         	{fn:'sin(number)',message:'返回给定角度的正弦值'}
			         ]
	},
	defaultLib:'聚合函数',
	closeAction :'hide',
   /**
   * 构造组件
   */
	constructor : function(config){
		Ext.ux.FnTools.superclass.constructor.call(this);
		//整理fn类型数组
		var fnTypes=[];
		for(var type in this.fnlib){
			fnTypes.push([type,type]);
		}
		var fndetailTextArea = new Ext.form.TextArea({
			value:this.fnlib[this.defaultLib][0].fn + '\r\n' + this.fnlib[this.defaultLib][0].message,
			style:'background:#FFFBE7;'
		});
		var fnListGrid = new Ext.grid.GridPanel({
			region:'center',
			border:true,
			autoExpandColumn:'fn',
			deferRowRender:false,
	        store:  new Ext.data.ArrayStore({
	        	fields: [{name: 'fn'},{name: 'message'}]
	        }),
	        columns: [{
	                    id       :'fn',
	                    header   : '函数', 
	                    sortable : true, 
	                    dataIndex: 'fn'
	                },{
	                    header   : '说明', 
	                    width    : 100,
	                    sortable : true, 
	                    dataIndex: 'message',
	                    hidden:true
	                }],
	        listeners:{rowclick:function(o,index,e){
				fndetailTextArea.setValue(o.getStore().getAt(index).data.fn + '\r\n' + o.getStore().getAt(index).data.message);
			}}
	 	});
		fnListGrid.getStore().loadData(this.getFn(this.defaultLib));
		
		
		var fnTypeComb = new Ext.ux.form.ComboBox({
 			fieldLabel:'选择类别',
	 		data:fnTypes,
	 		value:'聚合函数',
	 		context:this,
	 		listeners:{select:function(){
				fnListGrid.getStore().loadData(this.context.getFn(this.getValue()));
				fnListGrid.getSelectionModel().selectFirstRow();
				fnListGrid.fireEvent('rowclick',fnListGrid,0);
			}}
	 	});
		Ext.ux.FnTools.superclass.constructor.apply(
				this,[{
					width:500,
					height:400,
					layout:'border',
					title:'插入函数',
					defaults:{border:false,margins:'5 5 5 5'},
					listeners:{'show':function(){
						fnListGrid.getSelectionModel().selectFirstRow();
					}},
					tbar:[{
							xtype:'label',
							text:'选择函数分类：'
						},
						fnTypeComb
					],
					bbar:new Ext.Toolbar([
										    '->',
											{xtype:'button',text:'插     入',scope:this,handler:function(){
										    	config.submit(fnListGrid.getSelectionModel().getSelected().data.fn);
											}},
											'-',
											{xtype:'button',text:'关     闭',scope:this,handler:function(){
												this.close();
											}}
					]),
					items:[
						new Ext.Panel({
							region:'center',
							layout:'border',
							margins:'5 5 5 5',
							items:[fnListGrid,
								new Ext.Panel({
									height:100,
									region:'south',
									margins:'5 0 0 0',
									layout:'fit',
									border:false,
									items:[fndetailTextArea]
								})
							]
						})
					]
				}]
			);
	},
	getFn:function(type){
		var defaultData=[];
		for(var i = 0;i<this.fnlib[type].length;i++){
			defaultData.push([this.fnlib[type][i].fn,this.fnlib[type][i].message]);
		}
		return defaultData
	}
});

Ext.reg('fntools', Ext.ux.FnTools);
